package com.minisoft.pingan;

import cn.hutool.core.convert.Convert;
import cn.hutool.core.lang.Console;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.cell.CellUtil;
import cn.hutool.poi.excel.cell.FormulaCellValue;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

import java.util.*;

public class InsuranceStatistics {

    /**
     * 网点加投设置
     */
    static Map<String,Double> branchAddition = new HashMap<>();

    static {
        branchAddition.put("汇品", 0.12);
        branchAddition.put("和泰", 0.12);
        branchAddition.put("烁驰", 0.12);
        branchAddition.put("瑞涛", 0.12);
        branchAddition.put("军诚", 0.10);
        branchAddition.put("龙绘", 0.12);
        branchAddition.put("百洋", 0.12);
    }


    public static void main(String[] args) {
        doStatistics(true);
        doStatistics(false);
    }
    public static void doStatistics(boolean includeLeader) {
        // 读取数据Excel和网点人员Excel
        ExcelReader dataReader = new ExcelReader("D:\\网点汇总\\bake\\baodanResult-20250108113046.xlsx", 0);
        ExcelReader staffReader = new ExcelReader("D:\\网点汇总\\网点个代信息汇总.xlsx", 0);

        // 解析网点人员数据
        List<Map<String, Object>> staffList = staffReader.readAll();
        Map<String, String> staffIdToStaffName = new HashMap<>();
        Map<String, String> staffIdToBranch = new HashMap<>();
        Map<String, String> staffNameToLeader = new HashMap<>();

        for (Map<String, Object> staff : staffList) {
            Object staffId = staff.get("出单员id");
            if (staffId == null) {
                continue;
            }
            String id = staffId.toString();
            String name = staff.get("姓名").toString();
            String branch = staff.get("网点").toString();
            if (staff.get("团队长") != null) {
                String leader = staff.get("团队长").toString();
                staffNameToLeader.put(name, leader);
            }
            staffIdToStaffName.put(id, name);
            staffIdToBranch.put(id, branch);
        }

        // 解析数据并进行统计
        String month = null;
        List<Map<String, Object>> dataList = dataReader.readAll();
        Map<String, List<List<Object>>> branchData = new HashMap<>();
        for (Map<String, Object> data : dataList) {
            String staffId = data.get("出单员id").toString();
            if (StrUtil.isBlank(staffId)) {
                continue;
            }
            String branch = staffIdToBranch.get(staffId);
            String staffName = staffIdToStaffName.get(staffId);

            //如果没有对应的网点，则跳过
            if (branch == null) {
                continue;
            }

            // 提取所需列数据
            List<Object> rowData = new ArrayList<>();
            if (month == null) {
                month = data.get("日期").toString().substring(0, 7);
            }
            rowData.add(data.get("日期"));
            rowData.add(data.get("车牌号"));
            rowData.add(Convert.toDouble(data.get("商业险"),0.0));
            rowData.add(Convert.toDouble(data.get("交强险"),0.0));
            rowData.add(Convert.toDouble(data.get("意外险"),0.0));
            rowData.add(staffName);

            // 根据网点分组数据
            branchData.putIfAbsent(branch, new ArrayList<>());
            branchData.get(branch).add(rowData);
        }

        //对数据进行按日期升序排序
        branchData.forEach((branch, records) -> {
            records.sort(Comparator.comparing(row -> row.get(0).toString()));
        });

        // 写入数据到每个网点的Excel文件
        for (Map.Entry<String, List<List< Object>>> entry : branchData.entrySet()) {
            String branch = entry.getKey();
            List<List<Object>> records = entry.getValue();
            ExcelWriter writer = null;
            if (includeLeader) {
                writer = new ExcelWriter("D:\\网点汇总\\"+branch + month + "月份车险清单（含团队长汇总）.xlsx");
            } else {
                writer = new ExcelWriter("D:\\网点汇总\\"+branch + month + "月份车险清单.xlsx");
            }
            // 写入头部
            writer.writeRow(Arrays.asList(new String[]{"日期", "车牌号", "商业险", "交强险", "意外险", includeLeader ? "出单人姓名" : ""}));

            Map<String, Double> leaderSyxData = new HashMap<>();

            double syx = 0,jqx = 0,ywx = 0;
            // 写入数据
            for (List<Object> record : records) {
                syx += (double)record.get(2);
                jqx += (double)record.get(3);
                ywx += (double)record.get(4);
                String leader = staffNameToLeader.get(record.get(5).toString());
                if (StrUtil.isNotBlank(leader)) {
                    leaderSyxData.putIfAbsent(leader,0.0);
                    leaderSyxData.put(leader,leaderSyxData.get(leader) + (double)record.get(2));
                }
                if (!includeLeader) {
                    record.set(5,"");
                }
                writer.writeRow(record);
            }

            // 如果需要，在此处添加合计行
            writer.writeRow(Arrays.asList(new Object[]{"合计", "",
                    NumberUtil.round(syx,2),
                    NumberUtil.round(jqx,2),
                    NumberUtil.round(ywx,2),
                    NumberUtil.round(syx + jqx + ywx,2)}));

            //计算加投
            double addtion = branchAddition.getOrDefault(branch,0.0);
            writer.writeRow(Arrays.asList(new Object[]{"加投合计",
                    Convert.toInt(addtion * 100),
                    NumberUtil.round(syx * addtion,2),
                    "", "",""}));

            //团队长合计
            if (includeLeader) {
                for (Map.Entry<String, Double> team : leaderSyxData.entrySet()) {
                    writer.writeRow(Arrays.asList(new Object[]{team.getKey() + "合计",
                            "商业险：", NumberUtil.round(team.getValue(),2),
                            "加投：", NumberUtil.round(team.getValue() * addtion,2), "",}));
                }
            }
            writer.autoSizeColumnAll();
            writer.getCellStyle().setAlignment(HorizontalAlignment.LEFT);
            // 将数据写入到文件
            writer.flush();
            writer.close();
            Console.log("输出数据完成：{}，{}个",branch,records.size());
        }

        // 关闭读取的ExcelReader
        dataReader.close();
        staffReader.close();
    }
}
